package com.hualude.roadsign.repository;

import com.hualude.roadsign.dto.DTLampFaultDto;
import com.hualude.roadsign.dto.DTRequestDto;
import com.hualude.roadsign.dto.SignalLampFaultDto;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;

/**
 * Created by ding on 2017/8/4.
 */
public class SignalLampFaultRepositoryImpl {

    @PersistenceContext
    private EntityManager em;

    public List<DTLampFaultDto> getSignalLampFaultCheck4DT(DTRequestDto dtRequestDto, Integer actorId, Integer orgId) throws Exception {
        String sql = "SELECT LampFaultId,lf.SignalLampId,lf.LampPhoto,lf.LampCreatorId,lf.LampTime,lf.LampCheckerId," +
                "lf.LampMoney,lf.LampLevel,lf.LampType,lf.LampMainCheckerId,lf.LampFeedback,lf.LampReason,\n" +
                "lf.LampMainCheck,lf.LampCheck,lf.LampOrgId,lf.LampMainOrgId,OrgName AS lampOrgName,sl.SignalLampPhoto \n" +
                "FROM tb_lamp_fault lf,tb_signal_lamp sl,tb_org o\n" +
                "WHERE lf.LampOrgId=o.OrgId AND lf.SignalLampId=sl.SignalLampId " +
                " AND o.orgId like '%" + dtRequestDto.getSearch() + "%' AND lf.LampCheck=1 ";
//        根据主管审核人和系统管理员区分
        if (actorId == 7) {
            sql += " AND lf.LampMainOrgId=" + orgId + "";
        }
        sql += "  AND  lf.LampMainCheck IN (0,1,-1) \n" +
                " order by  find_in_set( lf.LampMainCheck ,'0,1,-1') , lf.LampTime DESC";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(DTLampFaultDto.class));
        List<DTLampFaultDto> lampFaultDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return lampFaultDtos;
    }

    public Integer getSignalLampFaultCheckCount4DT(DTRequestDto dtRequestDto, Integer actorId, Integer orgId) throws Exception {
        String sql = "SELECT count(*)\n" +
                "FROM tb_lamp_fault lf,tb_signal_lamp sl,tb_org o\n" +
                "WHERE lf.LampOrgId=o.OrgId AND lf.SignalLampId=sl.SignalLampId " +
                " AND o.orgId like '%" + dtRequestDto.getSearch() + "%' AND lf.LampCheck=1 ";
//        根据主管审核人和系统管理员区分
        if (actorId == 7) {
            sql += " AND lf.LampMainOrgId=" + orgId + "";
        }
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public List<DTLampFaultDto> getSignalLampFaultHandle4DT(DTRequestDto dtRequestDto, String actorIds, Integer orgId, Integer lampMaintenanceStatus) throws Exception {
        String sql = "SELECT u.userName as lampCreatorName,lf.LampFaultId,OrgName AS lampOrgName,lf.LampCreatorId,lf.LampTime,lf.LampCheckerId,lf.LampType,lf.LampLevel,lf.LampMaintenancePhoto,lf.LampMaintainerId,lf.LampReason," +
                "lf.LampMaintenanceStatus,lf.LampMainCheckTime,lf.LampMaintenanceExplain,sl.SignalLampLongitude,sl.SignalLampLatitude,lf.LampPhoto," +
                "lf.LampMainCheckerId,lf.SignalLampId,lf.LampMaintenanceContent,lf.LampFeedback,sl.SignalLampStartRoad,sl.SignalLampEndRoad," +
                "sl.SignalLampPhoto,sl.SignalLampArea,sl.SignalLampCity,sl.SignalLampProvince,lf.LampOrgId,lf.lampMaintenanceTime,lf.lampMoney\n" +
                "FROM tb_signal_lamp sl,tb_lamp_fault lf,tb_org o,tb_user u\n" +
                "WHERE sl.SignalLampId=lf.SignalLampId AND u.userId=lf.lampCreatorId AND lf.LampOrgId=o.OrgId AND lf.LampMainCheck in (1,2) \n" +
                "AND concat(sl.SignalLampStartRoad, sl.SignalLampEndRoad) like '%" + dtRequestDto.getSearch() + "%' AND lf.LampCheck in (1,2) ";
        //根据维修人员和系统管理员区分
        if (lampMaintenanceStatus != 3) {
            sql += " and lf.lampMaintenanceStatus = " + lampMaintenanceStatus + " ";
        }

        if (actorIds.indexOf('1') >= 0) {
            sql += "";
        } else if (actorIds.indexOf('3') >= 0 || actorIds.indexOf('5') >= 0) {
            sql += " AND lf.LampOrgId =" + orgId + "";
        } else if (actorIds.indexOf('7') >= 0) {
            sql += " AND lf.LampMainOrgId =" + orgId + "";
        }
        sql += " order by lf.lampMaintenanceStatus ASC ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(DTLampFaultDto.class));
        List<DTLampFaultDto> solarFaultDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return solarFaultDtos;
    }

    public Integer getSignalLampFaultHandleCount4DT(DTRequestDto dtRequestDto, String actorIds, Integer orgId, Integer lampMaintenanceStatus) throws Exception {
        String sql = "SELECT count(*)\n" +
                "FROM tb_signal_lamp sl,tb_lamp_fault lf,tb_org o\n" +
                "WHERE sl.SignalLampId=lf.SignalLampId AND lf.LampOrgId=o.OrgId AND lf.LampMainCheck=1\n" +
                "AND concat(sl.SignalLampStartRoad, sl.SignalLampEndRoad) like '%" + dtRequestDto.getSearch() + "%'  ";
        //根据维修人员和系统管理员区分
        if (lampMaintenanceStatus != 3) {
            sql += " and lf.lampMaintenanceStatus = " + lampMaintenanceStatus + " ";
        }
        if (actorIds.indexOf('1') >= 0) {
            sql += "";
        } else if (actorIds.indexOf('3') >= 0 || actorIds.indexOf('5') >= 0) {
            sql += " AND lf.LampOrgId =" + orgId + "";
        } else if (actorIds.indexOf('7') >= 0) {
            sql += " AND lf.LampMainOrgId =" + orgId + "";
        }
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public List<SignalLampFaultDto> getLampFaultCheckList4App(Integer orgId, String actorIds, Integer faultStatus, Integer count, Integer size) throws Exception {
        String sql = " SELECT lf.LampFaultId,lf.SignalLampId,lf.LampPhoto,lf.LampType,lf.LampLevel,lf.LampReason," +
                "lf.LampTime,lf.LampMainCheck,lf.LampCheck,lf.LampMainCheckTime\n" +
                "FROM tb_lamp_fault lf ";
        if (actorIds.indexOf("1") >= 0) {//系统管理员
            if (faultStatus == 0) {//待审核
                sql += " WHERE (lf.LampCheck =0) OR (lf.LampMainCheck =0 AND lf.LampCheck=1 )  ";
            } else if (faultStatus == 1) {//已通过
                sql += " WHERE  lf.LampMainCheck =1 AND lf.LampCheck=1  ";
            } else if (faultStatus == 2) {//未通过
                sql += " WHERE ( lf.LampCheck =-1) OR (lf.LampMainCheck =-1 AND lf.LampCheck=1 ) ";
            } else {
                return null;
            }
        } else if (actorIds.indexOf("3") >= 0) {//审核员
            if (faultStatus == 0) {//待审核
                sql += " WHERE lf.LampCheck=0 AND lf.LampOrgId=" + orgId + " ";
            } else if (faultStatus == 1) {//已通过
                sql += " WHERE lf.LampCheck=1 AND lf.LampOrgId=" + orgId + " ";
            } else if (faultStatus == 2) {//未通过
                sql += " WHERE lf.LampCheck=-1 AND lf.LampOrgId=" + orgId + " ";
            } else {
                return null;
            }
        } else if (actorIds.indexOf("7") >= 0) {//主管审核员
            if (faultStatus == 0) {//待审核
                sql += " WHERE lf.LampCheck=1 AND lf.LampMainCheck=0 AND lf.LampMainOrgId=" + orgId + " ";
            } else if (faultStatus == 1) {//已通过
                sql += " WHERE lf.LampCheck=1 AND lf.LampMainCheck=1 AND lf.LampMainOrgId=" + orgId + " ";
            } else if (faultStatus == 2) {//未通过
                sql += " WHERE lf.LampCheck=1 AND lf.LampMainCheck=-1 AND lf.LampMainOrgId=" + orgId + " ";
            } else {
                return null;
            }
        }
        sql += " ORDER BY lf.LampTime DESC ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignalLampFaultDto.class));
        return query.setFirstResult((count - 1) * size).setMaxResults(size).getResultList();
    }

    public Integer getLampReportFaultCount4DT(Integer actorId, Integer userId, Integer orgId, String strSearch) throws Exception {
        String sql = "select count(*) " +
                "FROM (((tb_lamp_fault lf JOIN tb_user u ON u.UserId=lf.LampCreatorId)\n" +
                "JOIN tb_org o ON o.OrgId=lf.LampOrgId)JOIN tb_signal_lamp sl ON sl.SignalLampId=lf.SignalLampId)\n" +
                "WHERE concat(sl.SignalLampStartRoad,sl.SignalLampEndRoad)like '%" + strSearch + "%'";
        if (actorId == 3) {
            sql += " and lf.LampOrgId=" + orgId + "";
        } else if (actorId == 6) {
            sql += " and lf.LampCreatorId=" + userId + "";
        }
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public List<DTLampFaultDto> getLampReportFault4DT(DTRequestDto dtRequestDto, Integer actorId, Integer userId, Integer orgId) throws Exception {
        String sql = "SELECT lf.*,u.UserName AS lampCreatorName,u2.UserName AS lampMaintainerName,u3.UserName " +
                "AS lampMainCheckerName, o.OrgName AS lampOrgName,l.signalLampPhoto from tb_lamp_fault lf left join tb_user u on" +
                " u.UserId=lf.LampCreatorId left join tb_user u2 on u2.userId = lf.LampMaintainerId " +
                "left join tb_user u3 on u3.userId=lf.LampMainCheckerId left join tb_org o on o.orgId=lf.LampOrgId " +
                "left join tb_signal_lamp l on l.signalLampId=lf.signalLampId " +
                "WHERE concat(l.signalLampStartRoad,l.signalLampEndRoad)like '%" + dtRequestDto.getSearch() + "%' ";
        if (actorId == 3) {
            sql += " and lf.LampOrgId=" + orgId + "";
        } else if (actorId == 6) {
            sql += " and lf.LampCreatorId=" + userId + "";
        }
        sql += " AND lf.LampCheck IN (0,1,2,-1) \n" +
                " order by find_in_set( lf.LampCheck ,'0,2,1,-1') , lf.LampTime DESC";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(DTLampFaultDto.class));
        List<DTLampFaultDto> machineFaultDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return machineFaultDtos;
    }

    public List<SignalLampFaultDto> getLampFaultReportList4App(Integer orgId, String actorIds, Integer faultStatus, Integer count, Integer size) throws Exception{
        String sql = "SELECT lf.LampPhoto,lf.SignalLampId,lf.LampType,lf.LampReason,lf.LampFaultId,lf.LampLevel,lf.LampTime,lf.LampCheck,lf.LampMainCheck\n" +
                "FROM tb_signal_lamp sl,tb_lamp_fault lf\n" +
                "WHERE sl.SignalLampId=lf.SignalLampId\n";
        //-1:未通过；0：待审核；1：已通过
        if(faultStatus==-1){
            sql += " AND (lf.LampCheck=-1 OR lf.LampMainCheck=-1) ";
        }else if(faultStatus==0){
            sql += " AND ((lf.LampCheck=1 AND lf.LampMainCheck=0) OR lf.LampCheck = 0) ";
        }else if(faultStatus==1){
            sql += " AND lf.LampMainCheck = 1 ";
        }
        if (actorIds.indexOf("1") >= 0) {
            sql += "";
        } else {
            sql += " AND lf.LampOrgId = " + orgId + " ";
        }
        sql += "ORDER BY LampTime DESC";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignalLampFaultDto.class));
        return query.setFirstResult((count - 1) * size).setMaxResults(size).getResultList();
    }

    public List<SignalLampFaultDto> getLampFaultHandleList4App(Integer orgId, Integer maintenanceStatus, String actorIds, Integer count, Integer size)throws Exception{
        String sql = "SELECT lf.LampFaultId,lf.SignalLampId,lf.LampPhoto,lf.LampType,lf.LampLevel," +
                "lf.LampReason,lf.LampMaintenanceStatus,lf.LampMainCheckTime,lf.LampMaintenancePhoto," +
                "lf.LampMaintenanceTime\n" +
                "FROM tb_lamp_fault lf " +
                "WHERE lf.LampMaintenanceStatus=" + maintenanceStatus + "";
        if (actorIds.indexOf("7") >= 0) {
            sql += " AND lf.LampMainOrgId=" + orgId + "";
        } else if (actorIds.indexOf("5") >= 0) {
            sql += " AND lf.LampOrgId=" + orgId + "";
        }
        sql += " ORDER BY lf.LampMainCheckTime DESC ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignalLampFaultDto.class));
        return query.setFirstResult((count - 1) * size).setMaxResults(size).getResultList();
    }
}
